library(flextable)
library(officer)
library(scales)
library(dplyr)
library(gt)


data <- readRDS("data_review_t3.rds")

services <- c("Waste_treatment", "Waste_collection",  
               "Transport", "Fire","Libraries", "Civil_protection", "Libraries", "Drinking_water",  
               "Sewer")

# Calculate descriptive statistics for each variable, round to two decimals, and add comma formatting
population_stats <- data %>%
  filter(service %in% services) %>%
  distinct() %>%
  summarise(
    Variable = "Population",
    Mean = round(mean(population, na.rm = TRUE), 2),
    SD = round(sd(population, na.rm = TRUE), 2),
    Min = round(min(population, na.rm = TRUE), 2),
    Max = round(max(population, na.rm = TRUE), 2),
    N = sum(!is.na(population))
  )

density_stats <- data %>%
  filter(service %in% services) %>%
  distinct() %>%
  summarise(
    Variable = "Density",
    Mean = round(mean(`Densitat..hab..km..`, na.rm = TRUE), 2),
    SD = round(sd(`Densitat..hab..km..`, na.rm = TRUE), 2),
    Min = round(min(`Densitat..hab..km..`, na.rm = TRUE), 2),
    Max = round(max(`Densitat..hab..km..`, na.rm = TRUE), 2),
    N = sum(!is.na(`Densitat..hab..km..`))
  )

turnout_stats <- data %>%
  filter(service %in% services) %>%
  distinct() %>%
  summarise(
    Variable = "Turnout %",
    Mean = round(mean(VOTANTS_PERCENT, na.rm = TRUE), 2),
    SD = round(sd(VOTANTS_PERCENT, na.rm = TRUE), 2),
    Min = round(min(VOTANTS_PERCENT, na.rm = TRUE), 2),
    Max = round(max(VOTANTS_PERCENT, na.rm = TRUE), 2),
    N = sum(!is.na(VOTANTS_PERCENT))
  )

debt_stats <- data %>%
  filter(service %in% services) %>%
  distinct() %>%
  summarise(
    Variable = "Debt p.c.",
    Mean = round(mean(EUROS_HABITANT, na.rm = TRUE), 2),
    SD = round(sd(EUROS_HABITANT, na.rm = TRUE), 2),
    Min = round(min(EUROS_HABITANT, na.rm = TRUE), 2),
    Max = round(max(EUROS_HABITANT, na.rm = TRUE), 2),
    N = sum(!is.na(EUROS_HABITANT))
  )

political_competition_stats <- data %>%
  filter(service %in% services) %>%
  distinct() %>%
  summarise(
    Variable = "Political Competition",
    Mean = round(mean(political_competition, na.rm = TRUE), 2),
    SD = round(sd(political_competition, na.rm = TRUE), 2),
    Min = round(min(political_competition, na.rm = TRUE), 2),
    Max = round(max(political_competition, na.rm = TRUE), 2),
    N = sum(!is.na(political_competition))
  )

hhi_stats <- data %>%
  filter(service %in% services) %>%
  distinct() %>%
  summarise(
    Variable = "HHI",
    Mean = round(mean(hhi, na.rm = TRUE), 2),
    SD = round(sd(hhi, na.rm = TRUE), 2),
    Min = round(min(hhi, na.rm = TRUE), 2),
    Max = round(max(hhi, na.rm = TRUE), 2),
    N = sum(!is.na(hhi))
  )

imc_stats <- data %>%
  filter(service %in% services) %>%
  distinct() %>%
  summarise(
    Variable = "IMC",
    Mean = round(mean(imc_dummy, na.rm = TRUE), 2),
    SD = round(sd(imc_dummy, na.rm = TRUE), 2),
    Min = round(min(imc_dummy, na.rm = TRUE), 2),
    Max = round(max(imc_dummy, na.rm = TRUE), 2),
    N = sum(!is.na(imc_dummy))
  )

year_stats <- data %>%
  filter(service %in% services) %>%
  distinct() %>%
  summarise(
    Variable = "Year",
    Mean = round(mean(year, na.rm = TRUE), 2),
    SD = round(sd(year, na.rm = TRUE), 2),
    Min = round(min(year, na.rm = TRUE), 2),
    Max = round(max(year, na.rm = TRUE), 2),
    N = sum(!is.na(year))
  )

library(scales)
# Combine all summaries into one table and format numbers with commas and two decimals
desc_stat <- bind_rows(population_stats, density_stats, turnout_stats, debt_stats, 
                       political_competition_stats, hhi_stats, imc_stats, year_stats) %>%
  mutate(
    Mean = comma(Mean, accuracy = 0.01),
    SD = comma(SD, accuracy = 0.01),
    Min = comma(Min, accuracy = 0.01),
    Max = comma(Max, accuracy = 0.01),
    N = comma(N)
  )

# Convert to a flextable for Word export with Garamond font
desc_stat_flextable <- desc_stat %>%
  flextable() %>%
  set_table_properties(width = 1, layout = "autofit") %>%
  font(fontname = "Garamond", part = "all") %>%
  fontsize(size = 12, part = "all") %>%
  align(align = "center", part = "all") %>%
  autofit()


library(officer)
# Create Word document and add the flextable
doc <- read_docx() %>%
  body_add_par("Descriptive Statistics", style = "heading 1") %>%
  body_add_flextable(desc_stat_flextable)

print(doc, "table3_review_jpp.docx")
